package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.Major;
import cn.edu.sdjzu.xg.bysj.service.DepartmentService;
import util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

public final class MajorDao {
    private static MajorDao majorDao = new MajorDao();

    private MajorDao() {
    }

    public static MajorDao getInstance() {
        // 静态变量MajorDao为空时，才创建MajorDao对象。
        // 第二次调用本方法MajorDao就不空了，不再创建对象。
        if (majorDao == null) {
            majorDao = new MajorDao();
        }
        return majorDao;
    }

    public Collection<Major> findAll() throws SQLException {
        Collection<Major> majors = new ArrayList<Major>();
        Connection conn = JdbcHelper.getConn();
        String major_sql = "select * from Major";
        PreparedStatement pstmt = conn.prepareStatement(major_sql);
        //根据id查找Major
        ResultSet resultSet = pstmt.executeQuery();
        while (resultSet.next()) {
            //根据id查找结果创建Major对象
            Major major = new Major(
                    resultSet.getInt("id"),
                    resultSet.getString("description"),
                    resultSet.getString("no"),
                    resultSet.getString("remarks"),
                    DepartmentService.getInstance().find(resultSet.getInt("department_id")));
            majors.add(major);
        }
        //关闭对象
        JdbcHelper.close(pstmt, conn);
        return majors;
    }

    public Major find(Integer id) throws SQLException {
        Connection conn = JdbcHelper.getConn();
        String deleteSchool_sql = "select * from Major where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(deleteSchool_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //根据id查找Major
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        //根据id查找结果创建Major对象
        Major department = new Major(
                id,
                resultSet.getString("description"),
                resultSet.getString("no"),
                resultSet.getString("remarks"),
                DepartmentService.getInstance().find(resultSet.getInt("department_id")));
        //关闭
        JdbcHelper.close(pstmt, conn);
        return department;
    }

    public boolean update(Major major) throws SQLException {
        Connection conn = JdbcHelper.getConn();
        String updateMajor_sql = "UPDATE Major SET no = ?,description = ?,remarks = ?,department_id = ? WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(updateMajor_sql);
        //为预编译参数赋值
        pstmt.setString(1, major.getNo());
        pstmt.setString(2, major.getDescription());
        pstmt.setString(3, major.getRemarks());
        pstmt.setInt(4, major.getDepartment().getId());
        pstmt.setInt(5, major.getId());

        //修改Major 并判断是否修改成功
        return pstmt.executeUpdate() > 0;
    }

    public boolean add(Major major) throws SQLException {
        Connection conn = JdbcHelper.getConn();
        String addMajor_sql = "INSERT INTO Major(description,no,remarks,department_id) " +
                "VALUES" + "(?,?,?,?)";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(addMajor_sql);
        //为预编译参数赋值
        pstmt.setString(1, major.getDescription());
        pstmt.setString(2, major.getNo());
        pstmt.setString(3, major.getRemarks());
        pstmt.setInt(4, major.getDepartment().getId());
        //添加Major 并判断是否添加成功
        return pstmt.executeUpdate() > 0;
    }

    public boolean delete(Major major) throws SQLException {
        Connection conn = JdbcHelper.getConn();
        String deleteMajor_sql = "delete from Major where no = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(deleteMajor_sql);
        //为预编译参数赋值
        pstmt.setString(1, major.getNo());
        //根据id删除Major 并判断是否删除成功
        return pstmt.executeUpdate() > 0;
    }

//    public static void main(String[] args) throws SQLException {
//        //查找id=2的Department
//        Department department = DepartmentService.getInstance().find(2);
//        System.out.println(department);
//
//        //测试增加
//        Major major = new Major(6,"6", "0206", "",department);
//        System.out.println(MajorService.getInstance().add(major));
//
//        //查找id=8的Major
//        System.out.println(MajorService.getInstance().find(4));
//
//        //测试修改
//        Major update_major = new Major(6,"update", "0206", "", department);
//        System.out.println(MajorService.getInstance().update(update_major));
//
//        //查找id=8的Major
//        System.out.println(MajorService.getInstance().find(6));
//
//        //测试删除
//        System.out.println(MajorService.getInstance().delete(6));
//
//        //查找全部
//        System.out.println(MajorService.getInstance().findAll());
//    }
}
